﻿'Description:装试油封管理接口
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yuxj
'create Date:2018-09-06
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient
Imports System.Text
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Public Class uLMSOilSealBusiness
    Implements uLMSOilSealIBusiness
    ''' <summary>
    ''' Add by Yuxj20190621
    ''' 获取默认件号ProductId
    ''' </summary>
    ''' <param name="productname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDeaultProduct(ByVal productname As String) As DataTable Implements uLMSOilSealIBusiness.GetDeaultProduct
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT p.productid,pb.productname FROM product p ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productid=pb.revofrcdid ")
        sb.AppendFormat("WHERE pb.productname='{0}' ", productname)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询发动机件号，需排除施工单创建的信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineProduct(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetEngineProduct
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT DISTINCT wf.ly2modle_node EngineProduct FROM workflow wf WHERE  wf.ly2modle_node<>'施工工艺' ")
        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat(" and wf.factoryid='{0}'  ", para("FactoryId"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim strList As String = String.Empty
        For i As Integer = dt.Rows.Count - 1 To 0 Step -1
            If dt.Rows(i).Item("EngineProduct").ToString.IndexOf(";") >= 0 Then
                strList += dt.Rows(i).Item("EngineProduct").ToString + ";"
                dt.Rows.RemoveAt(i)
            End If
        Next
        If strList.Length > 0 Then
            Dim str() As String = strList.Split(";")
            If str.Length > 0 Then
                For j As Integer = 0 To str.Length - 1
                    If str(j) = String.Empty Then
                        Continue For
                    ElseIf dt.Select(String.Format("EngineProduct='{0}'", str(j))).Length > 0 Then
                        Continue For
                    Else
                        dt.Rows.Add(str(j))
                    End If
                Next
            End If
        End If
        dt.AcceptChanges()
        Return dt
    End Function

    ''' <summary>
    ''' 获取部件件号列表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCompProduct(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetCompProduct
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT p.productid,pb.productname FROM enginepartplan ep ")
        sb.AppendLine("LEFT JOIN product p ON ep.productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid ")
        sb.AppendLine("WHERE ep.containerid IS NOT NULL and instr(wf.ly2modle_node,pb.productname)=0 ")
        If para.ContainsKey("EngineProduct") Then
            sb.AppendFormat(" AND INSTR(wf.ly2modle_node,'{0}')>0 ", para("EngineProduct"))
        End If
        sb.AppendLine("GROUP BY  p.productid,pb.productname  ORDER BY pb.productname ASC ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
    ''' <summary>
    ''' 获取零件件号
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPartProduct(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetPartProduct
        Dim sb As New StringBuilder
        Dim strSql As String =
            <string>
                SELECT NVL(p.productid,pb.revofrcdid) productid,pb.productname FROM bom b
                LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid
                LEFT JOIN productmateriallistitem PL ON b.bomid=pl.Bomid
                LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid
                LEFT JOIN product p ON p.Productid=pl.Productid
                LEFT JOIN productbase pb ON pb.productbaseid=NVL(p.productbaseid,pl.Productbaseid)
                LEFT JOIN workflow wf ON b.workflowid=wf.workflowid
                LEFT JOIN productworkflowmapping pw ON wf.workflowid=pw.workflowid
                LEFT JOIN product p1 ON p1.productid=pw.productid
                LEFT JOIN productbase pb1 ON pb1.productbaseid=p1.productbaseid                
                WHERE bt.billtypename='大小件目录' AND pl.Bigsmallpart='大零件' 
                and instr(wf.ly2modle_node,pb.productname)=0            
            </string>
        sb.AppendLine(strSql)
        If para.ContainsKey("CompProduct") Then
            sb.AppendFormat(" and pb1.productName='{0}' ", para("CompProduct"))
        End If
        If para.ContainsKey("EngineProduct") Then
            sb.AppendFormat(" and instr(wf.ly2modle_node,'{0}')>0  ", para("EngineProduct"))
        End If
        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat(" and wf.factoryid='{0}'  ", para("FactoryId"))
        End If
        sb.AppendLine("Group by NVL(p.productid,pb.revofrcdid),pb.productname  ORDER BY pb.productname ASC")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
    ''' <summary>
    ''' 获取零部件及整机油封信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealProductInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetOilSealProductInfo
        Dim dt As New DataTable
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                SELECT oc.oilsealcycleinfoname,om.oilsealmethodname,NVL(pm.productid,pbm.revofrcdid) eproductid,
                pbm.productname eproductname,pbc.productname cproductname,pbp.productname pproductname,el.*       
                 FROM ENGINEPARTOILSEAL el
                 LEFT JOIN oilsealcycleinfo oc ON el.oilsealcycleinfoid=oc.oilsealcycleinfoid
                 LEFT JOIN oilsealmethod om ON el.oilsealmethodid=om.oilsealmethodid
                 LEFT JOIN Userattribute u ON el.engineno=u.attributevalue 
                 INNER JOIN mfgorder m ON u.parentid=m.mfgorderid
                 LEFT JOIN product pm ON m.productid=pm.productid
                 LEFT JOIN productbase pbm ON pbm.productbaseid=NVL(pm.productbaseid,m.productbaseid)
                 LEFT JOIN product pc ON pc.productid=el.partid 
                 LEFT JOIN productbase pbc ON pc.productbaseid=pbc.productbaseid                  
                 LEFT JOIN product pp ON pp.productid=el.productid
                 LEFT JOIN productbase pbp ON pp.productbaseid=pbp.productbaseid
                 where u.userattributename='发动机'
            </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("EngineProduct") Then
            sb.AppendFormat(" and pbm.productName='{0}'  ", para("EngineProduct"))
        End If

        If para.ContainsKey("CPName") Then
            sb.AppendFormat(" and pbc.productname='{0}' ", para("CPName"))
        End If

        If para.ContainsKey("PPName") Then
            sb.AppendFormat(" and pbp.ProductName='{0}'  ", para("PPName"))
        End If

        dt = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function


    ''' <summary>
    ''' 获取油封周期
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealCycle() As DataTable Implements uLMSOilSealIBusiness.GetOilSealCycle
        Dim strsql As String = " select * "
        strsql += "FROM OilSealCycleInfo oil ORDER BY oil.oilsealcycleinfoname "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' 获取油封方法
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealMethod() As DataTable Implements uLMSOilSealIBusiness.GetOilSealMethod
        Dim strsql As String = " select DISTINCT oil.oilsealmethodid,oil.oilsealmethodname  "
        strsql += "FROM OilSealMethod oil ORDER BY oil.oilsealmethodname "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function
    ''' <summary>
    ''' 查询发动机油封信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealMachineInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetOilSealMachineInfo
        Dim strsql As String =
            <string>
                SELECT DISTINCT  u.attributevalue engineno,p.productid,pb.productname,p.description,
                        nvl2(el.partid,NULL,el.releasesealemployeeid) releasesealemployeeid,
                        nvl2(el.partid,NULL,el.enginepartoilsealid) enginepartoilsealid,
                        nvl2(el.partid,NULL,el.createdate) createdate,
                        nvl2(el.partid,NULL,el.startdate) startdate,
                        nvl2(el.partid,NULL,el.reminderdate) reminderdate,
                        nvl2(el.partid,NULL,el.operationdate) operationdate,
                        nvl2(el.partid,NULL,el.enddate) enddate, 
                        nvl2(el.partid,NULL,el.type) type,
                        nvl2(el.partid,NULL,el.isJZ) isJZ
                FROM Userattribute u
                INNER JOIN mfgorder m ON u.parentid=m.mfgorderid
                LEFT JOIN productbase pb1 ON pb1.productbaseid=m.productbaseid
                LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,m.productid)
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                LEFT JOIN enginepartoilseal el ON u.attributevalue=el.engineno
                WHERE u.userattributename='发动机'
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If Not para.ContainsKey("EngineNoEnd") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%'", para("EngineNo"))
        Else
            sb.AppendFormat(" and u.attributevalue between '{0}' and '{1}'", para("EngineNo"), para("EngineNoEnd"))
        End If
        Dim dtMachine As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        'Modify by Yuxj20190104 增加查询导入的油封信息
        sb.AppendLine("  UNION ")
        strsql = <string>
                     select DISTINCT engineno,null productid,null productname,null description,
                           
  CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.releasesealemployeeid ELSE NULL END releasesealemployeeid,
      CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.enginepartoilsealid ELSE NULL END enginepartoilsealid,
          CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.createdate ELSE NULL END createdate,
              CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.startdate ELSE NULL END startdate,
                  CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.reminderdate ELSE NULL END reminderdate,
                      CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.operationdate ELSE NULL END operationdate,
                          CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.enddate ELSE NULL END enddate,
                              CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.type ELSE NULL END type,
                                  CASE WHEN el.partid IS NULL AND el.productid IS NULL THEN el.isJZ ELSE NULL END isJZ                   
                     from enginepartoilseal el
                     LEFT JOIN Userattribute u ON el.engineno=u.attributevalue AND u.userattributename='发动机'

LEFT JOIN mfgorder m ON u.parentid=m.mfgorderid
LEFT JOIN productbase pb1 ON pb1.productbaseid=m.productbaseid
LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,m.productid)
LEFT JOIN
(SELECT ep.containerno,ep.productid,ep.mfgorderid FROM enginepartplan ep 
GROUP BY  ep.containerno,ep.productid,ep.mfgorderid ) ep  
ON m.mfgorderid=ep.mfgorderid AND p.productid != ep.productid

                     where ep.containerno IS NULL --not (el.productfamilyname is null) AND u.userattributeid IS NULL
                 </string>
        sb.AppendLine(strsql)
        If Not para.ContainsKey("EngineNoEnd") Then
            sb.AppendFormat(" and el.engineno like '%{0}%'", para("EngineNo"))
        Else
            sb.AppendFormat(" and el.engineno between '{0}' and '{1}'", para("EngineNo"), para("EngineNoEnd"))
        End If

        dtMachine = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        'If dtMachine.Rows.Count = 0 Then
        '    dtMachine = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        'End If

        para("Flag") = "M"
        Dim dtRes As DataTable = FilterDatatable(dtMachine, para)
        Return dtRes
    End Function

    ''' <summary>
    ''' 查询部件油封信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealCompInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetOilSealCompInfo
        Dim strsql As String =
            <string>
                SELECT DISTINCT u.attributevalue engineno,ep.containerno, pc.productid,pbc.productname,pc.description,
                DECODE(el.productid ,NULL, nvl2(el.partid,el.locationid,NULL),NULL) locationid,
                DECODE(el.productid ,NULL, nvl2(el.partid,el.releasesealemployeeid,NULL),NULL) releasesealemployeeid,
                DECODE(el.productid ,NULL, nvl2(el.partid,el.enginepartoilsealid,NULL),NULL) enginepartoilsealid,
                DECODE(el.productid ,NULL, nvl2(el.partid,el.createdate,NULL),NULL) createdate,
                DECODE(el.productid ,NULL, nvl2(el.partid,el.startdate,NULL),NULL) startdate,
                DECODE(el.productid,NULL,nvl2(el.partid,el.reminderdate,NULL),NULL) reminderdate,
                DECODE(el.productid,NULL,nvl2(el.partid,el.operationdate,NULL),NULL) operationdate,
                DECODE(el.productid,NULL,nvl2(el.partid,el.enddate,NULL),NULL) enddate,
                DECODE(el.productid,NULL,nvl2(el.partid,el.type,NULL),NULL) type,
                DECODE(el.productid,NULL,nvl2(el.partid,el.isJZ,NULL),NULL) isJZ               
                FROM Userattribute u
                inner JOIN mfgorder m ON u.parentid=m.mfgorderid

                LEFT JOIN productbase pb1 ON pb1.productbaseid=m.productbaseid
                LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,m.productid)
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                INNER JOIN
                (SELECT ep.containerno,ep.productid,ep.mfgorderid FROM enginepartplan ep 
                                    GROUP BY  ep.containerno,ep.productid,ep.mfgorderid ) ep  
                                    ON m.mfgorderid=ep.mfgorderid AND p.productid != ep.productid
                LEFT JOIN product pc ON pc.productid=ep.productid
                LEFT JOIN productbase pbc ON pbc.productbaseid=pc.productbaseid                
                LEFT JOIN enginepartoilseal el ON u.attributevalue=el.engineno and el.containerno=ep.containerno
                WHERE u.userattributename='发动机'
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If Not para.ContainsKey("EngineNoEnd") Then
            sb.AppendFormat(" and u.attributevalue = '{0}'", para("EngineNo"))
        Else
            sb.AppendFormat(" and u.attributevalue between '{0}' and '{1}'", para("EngineNo"), para("EngineNoEnd"))
        End If

        If para.ContainsKey("CPName") Then
            sb.AppendFormat(" and pbc.productname = '{0}' ", para("CPName"))
        End If

        sb.AppendLine("  UNION  ")
        strsql = <string>
                     select engineno,el.containerno,null productid,null productname,null description,
                            DECODE(el.productid ,NULL, nvl2(el.partid,el.locationid,NULL),NULL) locationid,
                            DECODE(el.productid ,NULL, nvl2(el.partid,el.releasesealemployeeid,NULL),NULL) releasesealemployeeid,
                            DECODE(el.productid ,NULL, nvl2(el.partid,el.enginepartoilsealid,NULL),NULL) enginepartoilsealid,
                            DECODE(el.productid ,NULL, nvl2(el.partid,el.createdate,NULL),NULL) createdate,
                            DECODE(el.productid ,NULL, nvl2(el.partid,el.startdate,NULL),NULL) startdate,
                            DECODE(el.productid,NULL,nvl2(el.partid,el.reminderdate,NULL),NULL) reminderdate,
                            DECODE(el.productid,NULL,nvl2(el.partid,el.operationdate,NULL),NULL) operationdate,
                            DECODE(el.productid,NULL,nvl2(el.partid,el.enddate,NULL),NULL) enddate,
                            DECODE(el.productid,NULL,nvl2(el.partid,el.type,NULL),NULL) type,
                            DECODE(el.productid,NULL,nvl2(el.partid,el.isJZ,NULL),NULL) isJZ                        
                     from enginepartoilseal el
                     LEFT JOIN Userattribute u ON el.engineno=u.attributevalue AND u.userattributename='发动机'
LEFT JOIN mfgorder m ON u.parentid=m.mfgorderid
LEFT JOIN productbase pb1 ON pb1.productbaseid=m.productbaseid
LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,m.productid)
LEFT JOIN
(SELECT ep.containerno,ep.productid,ep.mfgorderid FROM enginepartplan ep 
GROUP BY  ep.containerno,ep.productid,ep.mfgorderid ) ep  
ON m.mfgorderid=ep.mfgorderid AND p.productid != ep.productid
                     where  ep.containerno IS NULL--not (el.productfamilyname is null) AND
                 </string>
        sb.AppendLine(strsql)
        If Not para.ContainsKey("EngineNoEnd") Then
            sb.AppendFormat(" and el.engineno = '{0}'", para("EngineNo"))
        Else
            sb.AppendFormat(" and el.engineno between '{0}' and '{1}'", para("EngineNo"), para("EngineNoEnd"))
        End If

        If para.ContainsKey("CPName") Then
            sb.AppendFormat(" and el.containerno like '%{0}%' ", para("CPName"))
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        para("Flag") = "C"
        Dim dtRes As DataTable = FilterDatatable(dt, para)
        Return dtRes
    End Function

    Function FilterDatatable(ByVal dt As DataTable, ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.FilterDatatable
        dt.CaseSensitive = True
        dt = dt.DefaultView.ToTable(True)
        Dim StrColumn = String.Empty
        If para("Flag") = "M" Then
            StrColumn = "Engineno"
        ElseIf para("Flag") = "C" Then
            StrColumn = "ContainerNo"
        ElseIf para("Flag") = "P" Then
            'para("Flag") = "P"
            StrColumn = "ProductBatchno,ProductName"
        End If
        Dim dtFilter As New DataTable
        If para("Flag") = "P" Then
            dtFilter = dt.DefaultView.ToTable(True, "ProductBatchno", "ProductName")
        Else
            dtFilter = dt.DefaultView.ToTable(True, StrColumn)
        End If

        Dim drs As DataRow()
        Dim dvTemp As DataView
        Dim dtTemp As New DataTable
        Dim dtRes As DataTable = dt.Clone
        For Each dr As DataRow In dtFilter.Rows
            If StrColumn.IndexOf(",") >= 0 Then
                If Not IsDBNull(dr("ProductBatchno")) Then
                    drs = dt.Select(String.Format("ProductBatchno='{0}' and ProductName='{1}'", dr("ProductBatchno"), dr("ProductName")))
                Else
                    drs = dt.Select(String.Format("ProductBatchno is null and ProductName='{0}'", dr("ProductName")))
                End If
            Else
                drs = dt.Select(String.Format("{0}='{1}'", StrColumn, dr(StrColumn)))
            End If
            If drs Is Nothing Then
                Continue For
            End If
            If drs.Length = 0 Then
                Continue For
            End If

            dtTemp = drs.CopyToDataTable
            If drs.Length > 1 Then
                '删除标示为没有油封的一笔
                'Modify by Yuxj20181012 处理解封数据？
                Dim dtTemp2 = dtTemp.Copy

                dvTemp = dtTemp.Copy.DefaultView
                dvTemp.RowFilter = "startdate is not null "
                dvTemp.Sort = "createdate desc "
                dtTemp = dvTemp.ToTable

                '如果有多次油封，只取时间最大的一笔 Modify by Yuxj20181011
                If dtTemp.Rows.Count > 1 Then
                    Dim dtTempClone As DataTable = dtTemp.Clone
                    dtTempClone.Rows.Add(dtTemp.Rows(0).ItemArray)
                    dtTemp = dtTempClone
                End If
                '两种情况：前提：只有零件油封情况下；发动机查出多条，部件查出多条 。这两种情况上面的逻辑都会清楚掉。防止这种bug才有下面逻辑
                If (para("Flag") = "M" Or para("Flag") = "C") And dtTemp.Rows.Count = 0 Then
                    dtTemp.ImportRow(dtTemp2.Rows(0))
                End If

                dtTemp.AcceptChanges()
            End If

            If dtTemp.Rows.Count = 1 Then
                If Not IsDBNull(dtTemp.Rows(0)("releasesealemployeeid")) Then
                    dtTemp.Rows(0)("startdate") = System.DBNull.Value
                    dtTemp.Rows(0)("operationdate") = System.DBNull.Value
                    dtTemp.Rows(0)("reminderdate") = System.DBNull.Value
                    dtTemp.Rows(0)("enddate") = System.DBNull.Value
                End If
            End If
            dtRes.Merge(dtTemp)
        Next

        Return dtRes
    End Function

    ''' <summary>
    ''' 查询大零件油封信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealPartInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetOilSealPartInfo
        Dim strsql As String =
            <string>
                SELECT distinct  u.attributevalue engineno,ep.containerno,pm.productbatchno,p.productid cproductid,
                        pp.productid pproductid,pbp.ProductName,pp.description,
                        nvl2(el.productid ,el.releasesealemployeeid,NULL) releasesealemployeeid,
                        nvl2(el.productid ,el.createdate,NULL) createdate,
                        nvl2(el.productid ,el.enginepartoilsealid,NULL) enginepartoilsealid,          
                        nvl2(el.productid ,el.startdate,NULL) startdate,
                        nvl2(el.productid,el.reminderdate,NULL) reminderdate,
                        nvl2(el.productid,el.operationdate,NULL) operationdate,
                        nvl2(el.productid,el.enddate,NULL) enddate,
                        nvl2(el.productid,el.type,NULL) type,
                        nvl2(el.productid,el.isJZ,NULL) isJZ
                FROM USERATTRIBUTE U
                 INNER JOIN MFGORDER M
                    ON U.PARENTID = M.MFGORDERID
                 INNER JOIN (SELECT EP.CONTAINERNO,
                                    EP.PRODUCTID,
                                    EP.MFGORDERID
                               FROM ENGINEPARTPLAN EP
                              GROUP BY EP.CONTAINERNO,
                                       EP.PRODUCTID,
                                       EP.MFGORDERID) EP
                    ON M.MFGORDERID = EP.MFGORDERID                
                  LEFT JOIN PRODUCT P
                    ON P.PRODUCTID = EP.PRODUCTID
                  LEFT JOIN PRODUCTBASE PB
                    ON PB.PRODUCTBASEID = P.PRODUCTBASEID
                inner join 
                (SELECT pm.partmenuinfoid,cm.productbatchno,pm.productid,pm.containerno,pm.parttype FROM issueerpmaterial im 
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                where   cm.productbatchno IS NOT NULL
                GROUP BY pm.partmenuinfoid,cm.productbatchno,pm.productid,pm.containerno,pm.parttype) pm               
                    ON PM.CONTAINERNO = EP.CONTAINERNO
                  LEFT JOIN issueerpmaterial im 
                            ON pm.partmenuinfoid=im.issueerpmaterialid
                  LEFT JOIN PRODUCT PP
                    ON PP.PRODUCTID = PM.PRODUCTID
                  LEFT JOIN PRODUCTBASE PBP
                    ON PBP.PRODUCTBASEID = PP.PRODUCTBASEID
                  LEFT JOIN ENGINEPARTOILSEAL EL
                    ON PM.CONTAINERNO = EL.CONTAINERNO
                   AND EL.PARTID = P.PRODUCTID
                   AND PP.PRODUCTID = EL.PRODUCTID
                
                 WHERE PM.PARTTYPE = '0'
                   AND U.USERATTRIBUTENAME = '发动机' AND pbp.ProductName IS NOT NULL
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("EngineNo") Then
            sb.AppendFormat(" and u.attributevalue='{0}'", para("EngineNo"))
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and ep.ContainerNo='{0}'", para("ContainerNo"))
        End If

        sb.AppendLine(" union  ")
        sb.AppendLine(" SELECT  el.engineno,el.containerno,el.productbatchno,null cproductid, ")
        sb.AppendLine("el.productid pproductid,pbp.ProductName,pp.description,el.releasesealemployeeid, ")
        sb.AppendLine("el.createdate,el.enginepartoilsealid,el.startdate,el.reminderdate, ")
        sb.AppendLine("el.operationdate,el.enddate,el.type,el.isJZ ")
        sb.AppendLine(" from enginepartoilseal el  ")
        sb.AppendLine(" LEFT JOIN Userattribute u ON el.engineno=u.attributevalue AND u.userattributename='发动机'  ")
        sb.AppendLine(" LEFT JOIN PRODUCT PP  ON PP.PRODUCTID = el.PRODUCTID ")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PBP ON PBP.PRODUCTBASEID = PP.PRODUCTBASEID ")

        sb.AppendLine(<string>LEFT JOIN mfgorder m ON u.parentid=m.mfgorderid
LEFT JOIN productbase pb1 ON pb1.productbaseid=m.productbaseid
LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,m.productid)
LEFT JOIN
(SELECT ep.containerno,ep.productid,ep.mfgorderid FROM enginepartplan ep 
GROUP BY  ep.containerno,ep.productid,ep.mfgorderid ) ep  
ON m.mfgorderid=ep.mfgorderid AND p.productid != ep.productid</string>.Value)

        sb.AppendLine(" where ep.containerno Is NULL AND pbp.productname IS NOT NULL ") 'ep.containerno IS NULL
        If para.ContainsKey("EngineNo") Then
            sb.AppendFormat(" and el.engineno='{0}'", para("EngineNo"))
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and el.ContainerNo='{0}'", para("ContainerNo"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        para("Flag") = "P"
        Dim dtRes As DataTable = FilterDatatable(dt, para)
        Return dtRes
    End Function

    ''' <summary>
    ''' 插入油封操作表
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertEnginePartOilSeal(ByVal entity As BusinessRule.DTO.uMesOilSealDTO) As Boolean Implements uLMSOilSealIBusiness.InsertEnginePartOilSeal
        InsertEnginePartOilSeal = False
        Dim strsql As String =
            <string>
                INSERT INTO enginepartoilseal
                  (containerno, createdate, employeeid, enddate, engineno, enginepartoilsealid, isuse, 
                  oilsealcycleinfoid, oilsealmethodid, operationdate, partid, productbatchno, productid, qty, 
                  reminderdate, startdate,locationid,productfamilyname,IsJZ,type,notes)
                VALUES
                  (:containerno, sysdate, :employeeid, to_date(:enddate,'yyyy-mm-dd'), :engineno, :enginepartoilsealid,
                     :isuse, :oilsealcycleinfoid,                   :oilsealmethodid, to_date(:operationdate,'yyyy-mm-dd'),
                     :partid, :productbatchno, :productid, :qty,  to_date(:reminderdate,'yyyy-mm-dd'), 
                    to_date(:startdate,'yyyy-mm-dd'),:locationid,:productfamilyname,:IsJZ,:type,:notes)
            </string>
        Dim insertpar(19) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":containerno", entity.Containerno, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":employeeid", entity.Employeeid, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":enddate", entity.Enddate, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":engineno", entity.Engineno, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":enginepartoilsealid", entity.Enginepartoilsealid, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":isuse", entity.Isuse, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":oilsealcycleinfoid", entity.Oilsealcycleinfoid, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":oilsealmethodid", entity.Oilsealmethodid, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":operationdate", entity.Operationdate, OracleType.VarChar)

        If entity.Partid Is Nothing Then
            insertpar(9) = DatabaseHelper.NewParameter(":partid", "", OracleType.VarChar)
        Else
            insertpar(9) = DatabaseHelper.NewParameter(":partid", entity.Partid, OracleType.VarChar)
        End If

        insertpar(10) = DatabaseHelper.NewParameter(":productbatchno", entity.Productbatchno, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":productid", entity.Productid, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":qty", entity.Qty, OracleType.VarChar)

        insertpar(13) = DatabaseHelper.NewParameter(":reminderdate", entity.Reminderdate, OracleType.VarChar)
        insertpar(14) = DatabaseHelper.NewParameter(":startdate", entity.Startdate, OracleType.VarChar)

        insertpar(15) = DatabaseHelper.NewParameter(":locationid", IIf(IsNothing(entity.LocationId), String.Empty, entity.LocationId), OracleType.VarChar)
        insertpar(16) = DatabaseHelper.NewParameter(":productfamilyname", IIf(IsNothing(entity.ProductFamilyName), String.Empty, entity.ProductFamilyName), OracleType.VarChar)

        'Modify by Yuxj20190620
        insertpar(17) = DatabaseHelper.NewParameter(":IsJZ", IIf(IsNothing(entity.IsJZ), String.Empty, entity.IsJZ), OracleType.VarChar)
        insertpar(18) = DatabaseHelper.NewParameter(":type", IIf(IsNothing(entity.Type), String.Empty, entity.Type), OracleType.VarChar)
        insertpar(19) = DatabaseHelper.NewParameter(":notes", IIf(IsNothing(entity.Notes), String.Empty, entity.Notes), OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 插入油封人员工时表EnginePartOilSealWorker
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertEnginePartOilSealWorker(ByVal entity As BusinessRule.DTO.uMesOilSealDTO) As Boolean Implements uLMSOilSealIBusiness.InsertEnginePartOilSealWorker
        InsertEnginePartOilSealWorker = False
        Dim strsql As String =
            <string>
                INSERT INTO enginepartoilsealworker
                     (duration, employeeid, enginepartoilsealid)
                VALUES
                     (:duration, :employeeid, :enginepartoilsealid)
            </string>
        Dim insertpar(2) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":duration", entity.Duration, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":employeeid", entity.Employeeid, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":enginepartoilsealid", entity.Enginepartoilsealid, OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 插入件号油封工时表engineparttime
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertEnginePartTime(ByVal entity As BusinessRule.DTO.uMesOilSealDTO) As Boolean Implements uLMSOilSealIBusiness.InsertEnginePartTime
        InsertEnginePartTime = False
        Dim strsql As String =
            <string>
                INSERT INTO engineparttime
                  (createdate, employeeid, EnginePartTimeId, oilsealdurationperunit, oilsealmethodid, productname)
                VALUES
                  (sysdate, :employeeid, :EnginePartTimeId, :oilsealdurationperunit, :oilsealmethodid, :productname)
            </string>
        Dim insertpar(4) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":oilsealdurationperunit", entity.Oilsealdurationperunit, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":employeeid", entity.Employeeid, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":EnginePartTimeId", entity.EnginePartTimeId, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":oilsealmethodid", entity.Oilsealmethodid, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":productname", entity.Productname, OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 判断是否做了整机油封和部件油封
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function boolIsOilSealed(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSOilSealIBusiness.boolIsOilSealed
        Dim sb As New StringBuilder
        With sb
            .AppendLine(" SELECT el.releasesealemployeeid,el.enginepartoilsealid,el.createdate ")
            .AppendLine(" ,el.engineno,el.containerno,el.partid,el.productid ")
            .AppendLine(" FROM enginepartoilseal el WHERE el.isuse=0 ")
        End With
        If para.ContainsKey("EngineNo") Then
            sb.AppendFormat(" and EngineNo='{0}'", para("EngineNo"))
        End If
        'Modify by Yuxj20190620
        sb.AppendLine(" and (el.partid is null ")

        'Flag=C OR P
        If para("Flag") = "P" Then
            If para.ContainsKey("ContainerNo") Then
                sb.AppendFormat(" or (el.partid is not null and el.ContainerNo='{0}' and el.productid is null ) ", para("ContainerNo")) 'el.partid->el.productid
            End If
        End If
        sb.AppendLine(")")
        sb.AppendLine(" order by el.createdate desc")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            boolIsOilSealed = False
        Else
            If IsDBNull(dt.Rows(0)("releasesealemployeeid")) Then
                boolIsOilSealed = True
            Else
                boolIsOilSealed = False
            End If

        End If

    End Function

    ''' <summary>
    ''' Add by Yuxj20181012
    ''' 解除油封的方法
    ''' </summary>
    ''' <param name="OilSealId"></param>
    ''' <param name="ReleaseEmpId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UpdateEnginePartOilSeal(ByVal OilSealId As String, ByVal ReleaseEmpId As String) As Boolean Implements uLMSOilSealIBusiness.UpdateEnginePartOilSeal
        UpdateEnginePartOilSeal = False
        Dim strsql = " UPDATE enginepartoilseal el SET el.releasesealdate=SYSDATE,"
        strsql += String.Format(" el.releasesealemployeeid='{0}'  WHERE el.enginepartoilsealid in ('{1}') ", ReleaseEmpId, OilSealId.Replace(",", "','"))
        DatabaseHelper.ExecuteSql(strsql)
        UpdateEnginePartOilSeal = True
    End Function

    ''' <summary>
    ''' Add by Yuxj20190103
    ''' 获取油封位置
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilLocation(ByVal FactoryId As String) As DataTable Implements uLMSOilSealIBusiness.GetOilLocation
        Dim strsql As String = " SELECT l.locationname,l.locationid,l.factoryid,l.description FROM LOCATION l   "
        strsql += String.Format(" where l.factoryid='{0}' ", FactoryId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20190103
    ''' 判断是否导入
    ''' </summary>
    ''' <param name="EngineNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function BoolOilImport(ByVal EngineNo As String) As Boolean Implements uLMSOilSealIBusiness.BoolOilImport
        Dim strsql As String = " SELECT * from enginepartoilseal el   "
        strsql += String.Format(" where el.engineno='{0}' ", EngineNo)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 获取油封和人员信息 
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSOilSealIBusiness.GetOilSealInfo
        Dim strSql As String = <String>SELECT epw.employeeid workerid,epw.duration,ep.*  FROM  EnginePartOilSeal ep
        LEFT JOIN EnginePartOilSealWorker epw ON epw.enginepartoilsealid=ep.enginepartoilsealid
        WHERE 1=1</String>
        If para.Keys.Contains("EnginepartoilsealID") Then
            strSql += String.Format(" and ep.EnginepartoilsealID='{0}'", para("EnginepartoilsealID"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
End Class
